#include "mainwindow.h"
#include "ui_mainwindow.h"
#include "testsqlmodel.h"
#include <mainlayoutview.h>
#include <QSqlDatabase>
#include <QtDebug>
#include <QSqlQuery>

MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    // 当前qt支持的数据库
    qDebug() << "当前qt支持的数据库";
    qDebug() << QSqlDatabase::drivers();

    // sqlite数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("mysql.db");
    if(db.open())
    {
        qDebug() << "open database";
    }
    else
    {
        return;
    }
}

MainWindow::~MainWindow()
{
    delete ui;
}


void MainWindow::on_btn_Student_clicked()
{
#if 1
    QString sqlStr = "CREATE TABLE user (id integer PRIMARY KEY AUTOINCREMENT, name TEXT, password TEXT);";
    QSqlQuery myQuery;
    if(myQuery.exec(sqlStr))
    {
        qDebug() << "create table student user";
    }
    else
    {
        qDebug() << "faild";
    }
#endif
#if 0
    QSqlQuery query;
    // 创建student表
    query.exec("create table student1 (id int primary key, name varchar, course int)");
    query.exec("insert into student1 values(1, '李', 10)");
    query.exec("insert into student1 values(2, '马', 11)");
    query.exec("insert into student1 values(3, '孙', 12)");
    // 创建course表
    query.exec("create table course (id int primary key, name varchar, teacher varchar)");
    query.exec("insert into course values(10, '数学', '王老师')");
    query.exec("insert into course values(11, '英语', '张老师')");
    query.exec("insert into course values(12, '计算机', '李老师')");
#endif
}



void MainWindow::on_btn_query_clicked()
{
    QSqlQuery query("SELECT * FROM student");
    if(query.exec())
    {
        while(query.next())
        {
            QString strInfo0 = query.value(0).toString();
            QString strInfo1 = query.value(1).toString();
            QString strInfo2 = query.value(2).toString();
            qDebug() << "strInfo0" << strInfo0 << " " << "strInfo1" << strInfo1 << " " << "strInfo2" << strInfo2;
        }
    }
}


void MainWindow::on_btn_insert_clicked()
{
    // 第一种
//    QString strInsert = "INSERT INTO student(name, age) VALUES('李四', 12)";
//    QSqlQuery query;
//    if(query.exec(strInsert))
//    {
//        qDebug() << "insert sucess";
//    }
//    else
//    {
//        qDebug() << "insert failed";
//    }
    // 第二种 名称绑定
//    QString strInsert = "INSERT INTO student(name, age) VALUES(:name, :age)";
//    QSqlQuery insertQuery;
//    insertQuery.prepare(strInsert);
//    insertQuery.bindValue(":name", "王五");
//    insertQuery.bindValue(":age", 23);
//    if(insertQuery.exec())
//    {
//        qDebug() << "second insert success";
//    }
//    else
//    {
//        qDebug() << "second insert failed";
//    }

    // 第三种 位置绑定
//    QString strInsert = "INSERT INTO student(name, age) VALUES(?, ?)";
//    QSqlQuery insertQuery;
//    insertQuery.prepare(strInsert);
//    insertQuery.addBindValue("赵六");
//    insertQuery.addBindValue(32);
//    if(insertQuery.exec())
//    {
//        qDebug() << "third insert success";
//    }
//    else
//    {
//        qDebug() << "third insert failed";
//    }

    // 插入多条数据
    QString strInsert = "INSERT INTO student(name, age) VALUES(?, ?)";
    QVariantList vNames;
    vNames << "zhao" << "qian" << "sun" << "li";
    QVariantList vAges;
    vAges << 23 << 33 << 43 << 21;
    QSqlQuery insertQuery;
    insertQuery.prepare(strInsert);
    insertQuery.addBindValue(vNames);
    insertQuery.addBindValue(vAges);
    if(insertQuery.execBatch())
    {
        qDebug() << "insert 4 success";
    }
    else
    {
        qDebug() << "insert 4 failed";
    }
}


void MainWindow::on_btn_delete_clicked()
{
    QString strDelete = "DELETE FROM student WHERE id = 20";
    QSqlQuery query;
    if(query.exec(strDelete))
    {
        qDebug() << "delete sucsess";
    }
    else
    {
        qDebug() << "delete failed";
    }
}



void MainWindow::on_btn_modify_clicked()
{
    QString strModify = "UPDATE student SET name='LISI' where id=2";
    QSqlQuery query;
    if(query.exec(strModify))
    {
        qDebug() << "modify success";
    }
    else
    {
        qDebug() << "modify failed";
    }
}


void MainWindow::on_btn_jump_clicked()
{
    TestSqlModel *pView = new TestSqlModel();
    this->hide();
    pView->show();
}


void MainWindow::on_pushButton_clicked()
{
    MainLayoutView *pView = new MainLayoutView(this);
    this->hide();
    pView->show();
}

